package tingshu.repository;

import lombok.SneakyThrows;
import tingshu.data_object.TrackDO;
import tingshu.util.DBUtil;
import tingshu.util.Log;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author houdaiqin
 * @date 2022/07/13 20:28
 **/
public class TrackRepo {
    //将音频信息插入数据库
    @SneakyThrows
    public void insert(Integer uid, String title, String type, InputStream content) {
        String sql="insert into tracks(uid,title,type,content) values(?,?,?,?)";
        try(Connection c= DBUtil.connection()){
            try(PreparedStatement ps=c.prepareStatement(sql)){
                ps.setInt(1,uid);
                ps.setString(2,title);
                ps.setString(3,type);
                ps.setBinaryStream(4,content);

                Log.println("执行SQL"+ps.toString());
                ps.executeUpdate();
            }
        }
    }

    //根据分页查询音频信息
    @SneakyThrows
    public List<TrackDO> selectListByUidLimitOffset(int uid,int limit,int offset){
        List<TrackDO> list=new ArrayList<>();
        String sql="select tid,uid,title,type from tracks where uid=? order by tid desc limit ? offset ?";
        try(Connection c=DBUtil.connection()){
            try(PreparedStatement ps=c.prepareStatement(sql)){
                ps.setInt(1,uid);
                ps.setInt(2,limit);
                ps.setInt(3,offset);
                Log.println("执行SQL"+ps);

                try(ResultSet rs=ps.executeQuery()){
                    while (rs.next()){
                        TrackDO trackDO=new TrackDO(
                                rs.getInt("tid"),
                                rs.getInt("uid"),
                                rs.getString("title"),
                                rs.getString("type")
                        );
                        list.add(trackDO);
                    }
                }
            }
        }
        return list;
    }

    //查找出一个用户（uid）下所有音频的总数
    @SneakyThrows
    public int selectCountByUid(int uid){
        String sql="select count(*) from tracks where uid=?";
        try(Connection c=DBUtil.connection()){
            try(PreparedStatement ps=c.prepareStatement(sql)){
                ps.setInt(1,uid);

                Log.println("执行SQL"+ps);
                try(ResultSet rs=ps.executeQuery()){
                    rs.next();
                    return rs.getInt(1);
                }
            }
        }
    }

    //通过音频tidList查询出每首音频的信息
    @SneakyThrows
    public List<TrackDO> selectTrackListByTidList(List<Integer> tidList){
        String sqlFormat="select tid,uid,title,type from tracks where tid in (%s) order by tid";
        StringBuilder sb=new StringBuilder();
        for(Integer tid:tidList){
            sb.append(tid).append(",");
        }
        sb.delete(sb.length()-1,sb.length());
        String sql=String.format(sqlFormat,sb.toString());
        List<TrackDO> trackDOList=new ArrayList<>();
        try(Connection c=DBUtil.connection()){
            try(PreparedStatement ps=c.prepareStatement(sql)){
                Log.println("执行SQL"+ps.toString());
                try(ResultSet rs=ps.executeQuery()){
                    while (rs.next()){
                        TrackDO trackDO=new TrackDO(
                                rs.getInt("tid"),
                                rs.getInt("uid"),
                                rs.getString("title"),
                                rs.getString("type")
                        );
                        trackDOList.add(trackDO);
                    }
                }
            }
        }
        return trackDOList;
    }
}